﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Collections.ObjectModel;
using PM.Entity;
using PM.Common;
using System.Data.OracleClient;
using System.Data;

namespace PM.Services
{
    public class PortfolioServices
    {
        private static Portfolio GetPortfolioFromDataReader(IDataReader dataReader)
        {
            Portfolio retVal = null;
            if (dataReader != null && !dataReader.IsClosed)
            {

                Int64 int64Temp = 0;
                double dblTemp = 0;
                retVal = new Portfolio();


                retVal.ClientId = dataReader["CLIENT_ID"].ToString();
                retVal.SecurityId = dataReader["SECURITY_ID"].ToString();

                int64Temp = 0;
                if (Int64.TryParse(dataReader["QUANTITY"].ToString(), out int64Temp))
                    retVal.Quantity = int64Temp;

                dblTemp = 0;
                if (double.TryParse(dataReader["AVG_VALUE"].ToString(), out dblTemp))
                    retVal.AvgValue = dblTemp;

            }
            return retVal;
        }


        public static Portfolio GetPortfolioById(String clientId, String securityId)
        {
            Portfolio retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "SELECT * FROM PORTFOLIO where CLIENT_ID = :v1 and SECURITY_ID = :v2 ";
                cmd.Parameters.Add(":v1", OracleType.VarChar).Value = clientId;
                cmd.Parameters.Add(":v2", OracleType.VarChar).Value = securityId;

                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader.Read())
                    {
                        retVal = GetPortfolioFromDataReader(dataReader);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        public static Portfolio GetPortfolioById(String clientId, String securityId, OracleConnection con, OracleTransaction tran)
        {
            Portfolio retVal = null;
            //OracleConnection con = new OracleConnection();

            try
            {
                //con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                if (con.State != ConnectionState.Open)
                {
                    con.Open();
                }
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "SELECT * FROM PORTFOLIO WHERE CLIENT_ID = '" + clientId
                    + "' AND SECURITY_ID = '" + securityId + "'";
                cmd.Connection = con;
                cmd.Transaction = tran;
                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader.Read())
                    {
                        retVal = GetPortfolioFromDataReader(dataReader);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw exc;
            }
            finally
            {
                //con.Close();
            }
            return retVal;
        }

        public static PortfolioCollection GetPortfolioCollection()
        {
            PortfolioCollection retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "SELECT * FROM PORTFOLIO";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    retVal = new PortfolioCollection();
                    while (dataReader.Read())
                    {
                        Portfolio item = GetPortfolioFromDataReader(dataReader);
                        if (item != null)
                            retVal.Add(item);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        public static PortfolioCollection GetPortfolioCollectionByClientId(string clientId)
        {
            PortfolioCollection retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "SELECT * FROM PORTFOLIO where CLIENT_ID = '" + clientId + "'";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    retVal = new PortfolioCollection();
                    while (dataReader.Read())
                    {
                        Portfolio item = GetPortfolioFromDataReader(dataReader);
                        if (item != null)
                            retVal.Add(item);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        public static PortfolioCollection Get_Portfolio_Collection_By_ClientId_SecurityId(string clientId, string securityId)
        {
            PortfolioCollection retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                if (clientId != string.Empty && securityId != string.Empty)
                {
                    cmd.CommandText = "SELECT * FROM PORTFOLIO where CLIENT_ID = :v1 and SECURITY_ID = :v2 ";
                    cmd.Parameters.Add(":v1", OracleType.VarChar).Value = clientId;
                    cmd.Parameters.Add(":v2", OracleType.VarChar).Value = securityId;
                }
                else if (clientId != string.Empty)
                {
                    cmd.CommandText = "SELECT * FROM PORTFOLIO where CLIENT_ID = :v1 ";
                    cmd.Parameters.Add(":v1", OracleType.VarChar).Value = clientId;
                }
                else if (securityId != string.Empty)
                {
                    cmd.CommandText = "SELECT * FROM PORTFOLIO where SECURITY_ID = :v1 ";
                    cmd.Parameters.Add(":v1", OracleType.VarChar).Value = securityId;
                }
                else
                {
                    cmd.CommandText = "SELECT * FROM PORTFOLIO";
                }

                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    retVal = new PortfolioCollection();
                    while (dataReader.Read())
                    {
                        Portfolio item = GetPortfolioFromDataReader(dataReader);
                        if (item != null)
                            retVal.Add(item);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        public static DataTable GetPortfolioCollectionDataTable()
        {
            DataTable retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "SELECT * FROM PORTFOLIO";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader != null)
                    {
                        retVal = new DataTable();
                        retVal.Load(dataReader);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;

        }


        public static void Add(Portfolio portfolio)
        {
            OracleConnection con = new OracleConnection();
            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "INSERT INTO PORTFOLIO" +
                                    "(  CLIENT_ID, SECURITY_ID, QUANTITY, AVG_VALUE)" +
                                    "VALUES( :v1,    :v2,        :v3,      :v4)";

                cmd.Parameters.Add(":v1", OracleType.VarChar).Value = portfolio.ClientId;
                cmd.Parameters.Add(":v2", OracleType.VarChar).Value = portfolio.SecurityId;
                cmd.Parameters.Add(":v3", OracleType.Number).Value = portfolio.Quantity;
                cmd.Parameters.Add(":v4", OracleType.Number).Value = portfolio.AvgValue;

                cmd.Connection = con;
                cmd.ExecuteNonQuery();

            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
        }

        public static void Add(Portfolio portfolio, OracleConnection con, OracleTransaction tran)
        {
            //OracleConnection con = new OracleConnection();
            try
            {
                //con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                if (con.State != ConnectionState.Open)
                {
                    con.Open();
                }
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "INSERT INTO PORTFOLIO" +
                                    "(  CLIENT_ID, SECURITY_ID, QUANTITY, AVG_VALUE)" +
                                    "VALUES( :v1,    :v2,        :v3,      :v4)";

                cmd.Parameters.Add(":v1", OracleType.VarChar).Value = portfolio.ClientId;
                cmd.Parameters.Add(":v2", OracleType.VarChar).Value = portfolio.SecurityId;
                cmd.Parameters.Add(":v3", OracleType.Number).Value = portfolio.Quantity;
                cmd.Parameters.Add(":v4", OracleType.Number).Value = portfolio.AvgValue;

                cmd.Connection = con;
                cmd.Transaction = tran;
                cmd.ExecuteNonQuery();

            }
            catch (System.Exception exc)
            {
                throw exc;
            }
            finally
            {
                //con.Close();
            }
        }


        public static void Delete(String clientId, String securityId)
        {
            OracleConnection con = new OracleConnection();
            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "DELETE FROM PORTFOLIO WHERE CLIENT_ID = '" + clientId
                    + "' AND SECURITY_ID = '" + securityId + "'";
                cmd.Connection = con;

                cmd.ExecuteNonQuery();
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
        }

        public static void Delete(String clientId, String securityId, OracleConnection con, OracleTransaction tran)
        {
            //OracleConnection con = new OracleConnection();
            try
            {
                //con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                if (con.State != ConnectionState.Open)
                {
                    con.Open();
                }
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "DELETE FROM PORTFOLIO WHERE CLIENT_ID = '" + clientId
                    + "' AND SECURITY_ID = '" + securityId + "'";
                cmd.Connection = con;
                cmd.Transaction = tran;

                cmd.ExecuteNonQuery();
            }
            catch (System.Exception exc)
            {
                throw exc;
            }
            finally
            {
                //con.Close();
            }
        }

        public static void Update(Portfolio portfolio)
        {
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "UPDATE PORTFOLIO SET " +
                                  "QUANTITY = :v3, " +
                                  "AVG_VALUE = :v4 " +
                                  "WHERE CLIENT_ID = :v1 AND SECURITY_ID = :v2";


                cmd.Parameters.Add(":v1", OracleType.VarChar).Value = portfolio.ClientId;
                cmd.Parameters.Add(":v2", OracleType.VarChar).Value = portfolio.SecurityId;
                cmd.Parameters.Add(":v3", OracleType.Number).Value = portfolio.Quantity;
                cmd.Parameters.Add(":v4", OracleType.Number).Value = portfolio.AvgValue;


                cmd.Connection = con;
                cmd.ExecuteNonQuery();

            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
        }

        public static void Update(Portfolio portfolio, OracleConnection con, OracleTransaction tran)
        {
            //OracleConnection con = new OracleConnection();

            try
            {
                //con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                if (con.State != ConnectionState.Open)
                {
                    con.Open();
                }
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "UPDATE PORTFOLIO SET " +
                                  "QUANTITY = :v3, " +
                                  "AVG_VALUE = :v4 " +
                                  "WHERE CLIENT_ID = :v1 AND SECURITY_ID = :v2";


                cmd.Parameters.Add(":v1", OracleType.VarChar).Value = portfolio.ClientId;
                cmd.Parameters.Add(":v2", OracleType.VarChar).Value = portfolio.SecurityId;
                cmd.Parameters.Add(":v3", OracleType.Number).Value = portfolio.Quantity;
                cmd.Parameters.Add(":v4", OracleType.Number).Value = portfolio.AvgValue;


                cmd.Connection = con;
                cmd.Transaction = tran;
                cmd.ExecuteNonQuery();

            }
            catch (System.Exception exc)
            {
                throw exc;
            }
            finally
            {
                //con.Close();
            }
        }


    }
}
